package src.DAO;
import src.jdbc.PostgreSQL;
import src.Entities.ExperienciaLaboral;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;


/**
 *
 * @author Patty
 */
public class ExperienciaLaboralDAO {
        
  public String deleteExperienciaLaboral(int experienciaId){
        try {
            String sql = "DELETE FROM experiencia_laboral WHERE experiencia_id = ?;";
            PreparedStatement delete = PostgreSQL.getConnection().prepareStatement(sql);
            delete.setInt(1, experienciaId);
            return String.valueOf(delete.execute());
        } catch (Exception e) {
            return e.toString();
        }
    }

    public String insertExperienciaLaboral(ExperienciaLaboral experience) {
        try {
            String sql = "INSERT " +
                    "       INTO experiencia_laboral(experiencia_id, descripcion, fecha_inicio, fecha_termino, empresa, cargo, persona_id)" +
                    "     VALUES (NEXTVAL('experiencia_seq'), ?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement insert = PostgreSQL.getConnection().prepareStatement(sql);
            insert.setInt(1, experience.getExperienciaLaboralId());
            insert.setString(2, experience.getDescripcion());
            java.sql.Date fechaInicio = new java.sql.Date(experience.getFechaInicio().getTime());
            insert.setDate(3, fechaInicio);
            java.sql.Date fechaTermino = new java.sql.Date(experience.getFechaTermino().getTime());
            insert.setDate(4, fechaTermino);
            insert.setString(5,experience.getEmpresa());
            insert.setString(6, experience.getCargo());
            insert.setInt(7, experience.getPersonaId());
            return String.valueOf(insert.execute());
        } catch (Exception e) {
            return e.toString();
        }
    }

    public ArrayList selectExperienciaLaboral(String sql) {
        ArrayList results = new ArrayList();
        try {
            Connection con = PostgreSQL.getConnection();
            Statement select = con.createStatement();
            ResultSet query = select.executeQuery(sql);
            while (query.next()) {
                ExperienciaLaboral experience = new ExperienciaLaboral(query.getInt("experiencia_laboral_id"),
                        query.getString("descripcion"), query.getDate("fecha_inicio"), query.getDate("fecha_termino"),
                        query.getString("empresa"), query.getString("cargo"), query.getInt("persona_id"));
                results.add(experience);
            }
        } catch (Exception e) {
            System.out.println(e);
        }
        return results;
    }

    public String updateExperienciaLaboral(ExperienciaLaboral experience) {
        try {
            String sql = "UPDATE experiencia_laboral" +
                    "        SET descripcion = ?, fecha_inicio = ?, fecha_termino = ?, empresa = ?, cargo = ?, persona_id" +
                    "      WHERE experiencia_laboral_id = ?";
            PreparedStatement update = PostgreSQL.getConnection().prepareStatement(sql);
            update.setString(1, experience.getDescripcion());
            java.sql.Date fechaInicio = new java.sql.Date(experience.getFechaInicio().getTime());
            update.setDate(2, fechaInicio);
            java.sql.Date fechaTermino = new java.sql.Date(experience.getFechaTermino().getTime());
            update.setDate(3, fechaTermino);
            update.setString(4, experience.getEmpresa());
            update.setString(5, experience.getCargo());
            update.setInt(6, experience.getPersonaId());
            update.setInt(7, experience.getExperienciaLaboralId());
            return String.valueOf(update.execute());
        } catch (Exception e) {
            return e.toString();
        }
    }
}
